﻿using NPOI.XWPF.UserModel;
using SqlSugar;
using System;
using System.Linq;
using System.Collections.Generic;
using System.IO;
using System.Text;
using RuoVea.ExConfig;

namespace Db2Doc
{
    public class Db2DocServer
    {
        SqlSugarClient db;
        string _outPath = string.Empty;
        public Db2DocServer()
        {
            string connectionString = Appsettings.app("DbConnectionStrings", "ConnectionString")?.ToString();
            string dbType = Appsettings.app("DbConnectionStrings", "DbType")?.ToString().ToLower();

            _outPath = Directory.GetCurrentDirectory() + Appsettings.app("outpath").ToString();

            DbType DbType = DbType.Sqlite;

            switch (dbType)
            {
                case "mysql": DbType = DbType.MySql; break;
                case "sqlserver": DbType = DbType.SqlServer; break;
                case "sqlite": DbType = DbType.Sqlite; break;
                case "oracle": DbType = DbType.Oracle; break;
                case "postgresql": DbType = DbType.PostgreSQL; break;
                case "dm": DbType = DbType.Dm; break;
                case "kdbndp": DbType = DbType.Kdbndp; break;
            }
            db = new SqlSugarClient(new ConnectionConfig { ConnectionString = connectionString, DbType = DbType });
        }

        public Db2DocServer(string connectionString, string dbType, string outPath)
        {
            if(string.IsNullOrWhiteSpace(outPath))
                _outPath = Appsettings.app("DbConnectionStrings", "DbType")?.ToString().ToLower();
            else
            _outPath = outPath;

            DbType DbType = DbType.Sqlite;
            switch (dbType)
            {
                case "mysql": DbType = DbType.MySql; break;
                case "sqlserver": DbType = DbType.SqlServer; break;
                case "sqlite": DbType = DbType.Sqlite; break;
                case "oracle": DbType = DbType.Oracle; break;
                case "postgresql": DbType = DbType.PostgreSQL; break;
                case "dm": DbType = DbType.Dm; break;
                case "kdbndp": DbType = DbType.Kdbndp; break;
            }
            db = new SqlSugarClient(new ConnectionConfig { ConnectionString = connectionString, DbType = DbType });
        }

        public static Db2DocServer Init()
        {
            return new Db2DocServer();
        }

        public static Db2DocServer Init(string connectionString, string dbType, string outPath)
        {
            return new Db2DocServer(connectionString, dbType, outPath);
        }
        /// <summary>
        /// 生成word文档
        /// </summary>
        /// <param name="tables">数据库数据表的列表</param>
        /// <param name="tableDetails">表的详情</param>
        /// <param name="procs">存储过程</param>
        /// <param name="views">视图</param>
        public void BuildToWord()
        {
            XWPFDocument doc = new XWPFDocument();      //创建新的word文档

            XWPFParagraph p1 = doc.CreateParagraph();   //向新文档中添加段落
            p1.IsWordWrapped = false;

            p1.Alignment = ParagraphAlignment.CENTER;
            XWPFRun r1 = p1.CreateRun();
            r1.FontFamily = "微软雅黑";
            r1.FontSize = 22;
            r1.IsBold = true;
            //向该段落中添加文字
            r1.SetText("数据库说明文档");

            //XWPFParagraph p2 = doc.CreateParagraph();  
            //XWPFRun r2 = p2.CreateRun();
            //r2.SetText("测试段落二");
            List<DbTableInfo> tables = GetTables();
            #region 创建一个表格
            TablesToWord(doc, tables);
            #endregion

            #region 存储过程
            ProcoWord(doc);
            #endregion

            #region 视图
            ViewWord(doc);
            #endregion



            FileStream sw = File.Create(_outPath); //...
            doc.Write(sw);                              //...
            sw.Close();                                 //在服务端生成文件
        }

        #region 创建一个表格
        /// <summary>
        ///  创建一个表格
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="tables"></param>
        /// <param name="procs"></param>
        /// <param name="views"></param>
        public void TablesToWord(XWPFDocument doc, List<DbTableInfo> tables)
        {
            int tabeFontSize = 10;

            int headFontSize = 8;
            bool headIsBold = true;
            string headFontFamily = "微软雅黑";

            int bodyFontSize = 8;
            bool bodyIsBold = false;

            #region 创建一个表格
            if (tables.Count > 0)
            {
                tables = tables.OrderBy(x => x.Name).ToList();
                int itb = 0;
                foreach (var item in tables)
                {
                    Console.WriteLine("表名:" + item.Name);
                    itb++;
                    XWPFParagraph p3 = doc.CreateParagraph();   //向新文档中添加段落
                    p3.Alignment = ParagraphAlignment.LEFT;
                    XWPFRun r3 = p3.CreateRun();                //向该段落中添加文字
                    r3.FontFamily = "微软雅黑";
                    r3.FontSize = tabeFontSize;
                    r3.IsBold = true;
                    r3.SetText($"表{itb}、" + item.Description+" ("+ item.Name+") ");

                    //从第二行开始 因为第一行是表头
                    int i = 1;
                    List<TableDetailModel> tableDetails = GetTableDetail(item.Name);

                    XWPFTable table = doc.CreateTable(tableDetails.Count + 1, 7);
                    table.Width = 5000;

                    #region 设置表头               

                    XWPFParagraph pI1 = table.GetRow(0).GetCell(0).GetParagraphArray(0);
                    pI1.Alignment = ParagraphAlignment.LEFT;

                    XWPFRun rI1 = pI1.CreateRun();
                    rI1.SetText("名称");
                    rI1.FontFamily = headFontFamily;
                    rI1.FontSize = headFontSize;
                    rI1.IsBold = headIsBold;


                    XWPFParagraph pI3 = table.GetRow(0).GetCell(1).GetParagraphArray(0);
                    pI3.Alignment = ParagraphAlignment.LEFT;
                    XWPFRun rI3 = pI3.CreateRun();
                    rI3.SetText("主键");
                    rI3.FontFamily = headFontFamily;
                    rI3.FontSize = headFontSize;
                    rI3.IsBold = headIsBold;

                    XWPFParagraph pI4 = table.GetRow(0).GetCell(2).GetParagraphArray(0);
                    pI4.Alignment = ParagraphAlignment.LEFT;
                    XWPFRun rI4 = pI4.CreateRun();
                    rI4.SetText("类型");
                    rI4.FontFamily = headFontFamily;
                    rI4.FontSize = headFontSize;
                    rI4.IsBold = headIsBold;



                    XWPFParagraph pI5 = table.GetRow(0).GetCell(3).GetParagraphArray(0);
                    pI5.Alignment = ParagraphAlignment.LEFT;
                    XWPFRun rI5 = pI5.CreateRun();
                    rI5.SetText("长度");
                    rI5.FontFamily = headFontFamily;
                    rI5.FontSize = headFontSize;
                    rI5.IsBold = headIsBold;

                    XWPFParagraph pI6 = table.GetRow(0).GetCell(4).GetParagraphArray(0);
                    pI6.Alignment = ParagraphAlignment.LEFT;
                    XWPFRun rI6 = pI6.CreateRun();
                    rI6.SetText("不允许空");
                    rI6.FontFamily = headFontFamily;
                    rI6.FontSize = headFontSize;
                    rI6.IsBold = headIsBold;


                    XWPFParagraph pI7 = table.GetRow(0).GetCell(5).GetParagraphArray(0);
                    pI7.Alignment = ParagraphAlignment.LEFT;
                    XWPFRun rI7 = pI7.CreateRun();
                    rI7.SetText("默认值");
                    rI7.FontFamily = headFontFamily;
                    rI7.FontSize = headFontSize;
                    rI7.IsBold = headIsBold;

                    XWPFParagraph pI8 = table.GetRow(0).GetCell(6).GetParagraphArray(0);
                    pI8.Alignment = ParagraphAlignment.LEFT;
                    XWPFRun rI8 = pI8.CreateRun();
                    rI8.SetText("说明");
                    rI8.FontFamily = headFontFamily;
                    rI8.FontSize = headFontSize;
                    rI8.IsBold = headIsBold;

                    #endregion

                    if (tableDetails != null && tableDetails.Count > 0)
                    {
                        foreach (var itm in tableDetails)
                        {
                            //第一列
                            XWPFParagraph pIO = table.GetRow(i).GetCell(0).GetParagraphArray(0);
                            XWPFRun rIO = pIO.CreateRun();
                            rIO.FontSize = bodyFontSize;
                            rIO.IsBold = bodyIsBold;
                            rIO.SetText(itm.Title);

                            XWPFParagraph pIO4 = table.GetRow(i).GetCell(1).GetParagraphArray(0);
                            XWPFRun rIO4 = pIO4.CreateRun();
                            rIO4.FontSize = bodyFontSize;
                            rIO4.IsBold = bodyIsBold;
                            rIO4.SetText(itm.isPK.ToString());

                            XWPFParagraph pIO5 = table.GetRow(i).GetCell(2).GetParagraphArray(0);
                            XWPFRun rIO5 = pIO5.CreateRun();
                            rIO5.FontSize = bodyFontSize;
                            rIO5.IsBold = bodyIsBold;
                            rIO5.SetText(itm.FieldType);

                            XWPFParagraph pIO6 = table.GetRow(i).GetCell(3).GetParagraphArray(0);
                            XWPFRun rIO6 = pIO6.CreateRun();
                            rIO6.FontSize = bodyFontSize;
                            rIO6.IsBold = bodyIsBold;
                            rIO6.SetText(itm.fieldLenth.ToString());

                            XWPFParagraph pIO7 = table.GetRow(i).GetCell(4).GetParagraphArray(0);
                            XWPFRun rIO7 = pIO7.CreateRun();
                            rIO7.FontSize = bodyFontSize;
                            rIO7.IsBold = bodyIsBold;
                            rIO7.SetText(itm.isAllowEmpty.ToString());

                            XWPFParagraph pIO8 = table.GetRow(i).GetCell(5).GetParagraphArray(0);
                            XWPFRun rIO8 = pIO8.CreateRun();
                            rIO8.FontSize = bodyFontSize;
                            rIO8.IsBold = bodyIsBold;
                            rIO8.SetText(itm.defaultValue.ToString());

                            XWPFParagraph pIO9 = table.GetRow(i).GetCell(6).GetParagraphArray(0);
                            XWPFRun rIO9 = pIO9.CreateRun();
                            rIO9.FontSize = bodyFontSize;
                            rIO9.IsBold = bodyIsBold;
                            rIO9.SetText(itm.fieldDesc);

                            i++;
                        }
                    }
                }
            }

            #endregion
        }
        #endregion

        #region 存储过程
        /// <summary>
        /// 存储过程
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="tables"></param>
        /// <param name="procs"></param>
        /// <param name="views"></param>
        public void ProcoWord(XWPFDocument doc)
        {
            List<ProcModel> procs = GetProcList();
            if (procs.Count > 0)
            {
                #region 存储过程
                XWPFParagraph p2 = doc.CreateParagraph();
                XWPFRun r2 = p2.CreateRun();
                r2.FontSize = 16;
                r2.SetText("存储过程");

                foreach (var item in procs)
                {
                    //存储过程名称
                    XWPFParagraph pro1 = doc.CreateParagraph();
                    XWPFRun rpro1 = pro1.CreateRun();
                    rpro1.FontSize = 14;
                    rpro1.IsBold = true;
                    rpro1.SetText("存储过程名称：" + item.procName);
                    //存储过程 详情
                    XWPFParagraph pro2 = doc.CreateParagraph();
                    XWPFRun rpro2 = pro2.CreateRun();
                    rpro2.FontSize = 12;
                    rpro2.SetText(item.proDerails);
                }
            }
            #endregion
        }
        #endregion

        #region 视图
        /// <summary>
        /// 视图
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="tables"></param>
        /// <param name="procs"></param>
        /// <param name="views"></param>
        public void ViewWord(XWPFDocument doc)
        {
            List<ViewModel> views = GetViewList();
            #region 视图
            if (views.Count > 0)
            {
                XWPFParagraph v2 = doc.CreateParagraph();
                XWPFRun vr2 = v2.CreateRun();
                vr2.FontSize = 16;
                vr2.SetText("视图");
                foreach (var item in views)
                {
                    //存储过程名称
                    XWPFParagraph vro1 = doc.CreateParagraph();
                    XWPFRun vpro1 = vro1.CreateRun();
                    vpro1.FontSize = 14;
                    vpro1.IsBold = true;
                    vpro1.SetText("视图名称：" + item.viewName);
                    //存储过程 详情
                    XWPFParagraph vro2 = doc.CreateParagraph();
                    XWPFRun vpro2 = vro2.CreateRun();
                    vpro2.FontSize = 12;
                    vpro2.SetText(item.viewDerails);
                }
            }
            #endregion
        }
        #endregion

        #region 获取特定数据库里面的存储过程
        /// <summary>
        /// 获取特定数据库里面的存储过程
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public List<ProcModel> GetProcList()
        {
            var list = new List<ProcModel>();
            string sql = @"  select name as procName, (select text from syscomments where id=OBJECT_ID(name)) as proDerails
                         from dbo.sysobjects  o  where OBJECTPROPERTY(id, N'IsProcedure') = 1 order by name  ";
            try
            {
                // http://www.cnblogs.com/minideas/archive/2009/10/29/1591891.html
                list = db.SqlQueryable<ProcModel>(sql).ToList();
            }
            catch
            {

            }
            return list;
        }
        #endregion

        #region 获取特定数据库里面的视图
        /// <summary>
        /// 获取特定数据库里面的视图
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public List<ViewModel> GetViewList()
        {

            List<DbTableInfo> views = db.DbMaintenance.GetViewInfoList();

            var list = new List<ViewModel>();
            string sql = @"  select name as viewName, (select text from syscomments where id=OBJECT_ID(name)) as viewDerails
                         from dbo.sysobjects  o  where OBJECTPROPERTY(id, N'IsView') = 1 order by name  ";
            try
            {
                // http://www.cnblogs.com/minideas/archive/2009/10/29/1591891.html
                list = db.SqlQueryable<ViewModel>(sql).ToList();
            }
            catch
            {
            }
            return list;
        }
        #endregion

        #region 获取字段的信息
        /// <summary>
        /// 获取字段的信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<TableDetailModel> GetTableDetail(string tableName)
        {
            var lists = db.DbMaintenance.GetColumnInfosByTableName(tableName);

            List<TableDetailModel> list = new List<TableDetailModel>();
            int j = 1;
            foreach (var x in lists)
            {
                TableDetailModel item = new TableDetailModel();
                //item.index = j;
                item.Title = x.DbColumnName;
                //item.isMark = x.IsIdentity ;
                item.isPK = x.IsPrimarykey ? "true" : "";
                item.FieldType = x.DataType ?? "";
                item.fieldLenth = x.Length;
                item.isAllowEmpty = x.IsNullable ? "true" : "";
                item.defaultValue = x.DefaultValue ?? "";

                item.fieldDesc = x.ColumnDescription ?? "";
                list.Add(item);
                j++;
            }
            return list;


            //var list = new List<TableDetailModel>();

            //StringBuilder sb = new StringBuilder();
            //sb.Append("SELECT [index] = a.colorder,    Title = a.name,    isMark =        CASE    WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '1' ELSE '0' END, ");
            //sb.Append("isPK =  CASE   WHEN EXISTS(SELECT  1  FROM sysobjects WHERE xtype = 'PK' AND parent_obj = a.id AND name IN(SELECT name  FROM sysindexes WHERE indid IN(SELECT indid  FROM sysindexkeys  WHERE id = a.id AND colid = a.colid)) ) THEN '1' ELSE '0' END, ");
            //sb.Append("	FieldType = b.name,fieldLenth = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),isAllowEmpty =  CASE   WHEN a.isnullable = 1 THEN '1' ELSE '0' END, defaultValue = ISNULL(e.text, ''), fieldDesc = ISNULL(g.[value], '') ");
            //sb.Append("FROM syscolumns a LEFT JOIN systypes b  ON a.xusertype = b.xusertype INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties' LEFT JOIN syscomments e ON a.cdefault = e.id ");
            //sb.Append("LEFT JOIN sys.extended_properties g ON a.id = G.major_id AND a.colid = g.minor_id LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0");
            ////--如果只查询指定表,加上此红色where条件，tablename是要查询的表名；去除红色where条件查询说有的表信息
            ////sb.Append("WHERE d.name = '" + tableName + "' ORDER BY a.id, a.colorder, d.name");
            //try
            //{
            //    list = db.SqlQueryable<TableDetailModel>(sb.ToString()).ToList();

            //}
            //catch(Exception ex)
            //{ 

            //}

            //return list;
        }
        #endregion

        #region 获取表名称
        /// <summary>
        /// 获取表名称
        /// </summary>
        /// <param name="db"></param>
        /// <returns></returns>
        public List<DbTableInfo> GetTables()
        {
            try
            {
                return db.DbMaintenance.GetTableInfoList().ToList();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        #endregion
    }
}
